import logging
from typing import Annotated
from fastapi import Depends
from sqlmodel import *
from sqlalchemy.exc import OperationalError, NotSupportedError
from apscheduler.schedulers.background import BackgroundScheduler
from app.config import get_settings
from app.utils.password import get_password_hash
from app.utils.uuid import generate_unique_id
from app.database.models import Account, AccountIdentity

scheduler = BackgroundScheduler()
engine = create_engine(get_settings().POSTGRESQL_URL, echo=False)
uvicorn_logger = logging.getLogger('uvicorn')


def _init_database_tables():
    """
    初始化数据库，创建所有表结构
    """
    SQLModel.metadata.create_all(engine)
    uvicorn_logger.info("Init database tables")


def _init_super_admin():
    """
    初始化超级管理员（保证数据库至少有一个管理员）
    """
    with Session(engine) as session:
        is_exist_super = session.exec(select(Account).where(
            Account.identity == AccountIdentity.SUPER_USER.value)).first()

        # 如果没有超级管理员，则创建一个新的超级管理员
        if not is_exist_super:
            username = get_settings().SUPER_USERNAME
            email = get_settings().SUPER_EMAIL
            password = get_settings().SUPER_PASSWORD
            hash_password = get_password_hash(password)

            account_id = generate_unique_id(optional_params={
                username: username,
                email: email
            })

            new_super_admin = Account(
                aid=account_id,
                username=username,
                email=email,
                password=hash_password,
                identity=AccountIdentity.SUPER_USER.value,  # 超管身份
                max_apps=100,
                avatar="",
                phone="",
                remark="初始化超级管理员"
            )
            session.add(new_super_admin)
            session.commit()
            session.refresh(new_super_admin)
            uvicorn_logger.info(f"Super admin initialized: {new_super_admin.username}")
        else:
            uvicorn_logger.info("Super admin already exists. Skipping initialization.")


def _clean_old_records():
    """Clean records older than 31 days"""
    with Session(engine) as session:
        try:
            # Use database time for consistency
            cleanup_sql = text("""
                            DELETE FROM tb_app_login_times 
                            WHERE create_time < date_trunc('month', NOW())
                            RETURNING *
                        """)

            result = session.execute(cleanup_sql)
            rowcount = result.rowcount
            session.commit()

            uvicorn_logger.info(f"Cleaned {rowcount} expired records")
        except Exception as e:
            uvicorn_logger.error(f"Cleanup failed: {str(e)}")
            session.rollback()


def _init_scheduler():
    """Initialize application-level scheduler"""
    if not scheduler.running:
        try:
            [hour, minute] = get_settings().CLEAN_CARD_LOGIN_TIMES.split(':', 1)

            scheduler.add_job(
                _clean_old_records,
                'cron',
                hour=int(hour),
                minute=int(minute),
                misfire_grace_time=3600,
                id='record_cleanup_job'
            )
            scheduler.start()
            uvicorn_logger.info(
                f"Scheduled task initialized: daily cleanup at {hour.zfill(2)}:{minute.zfill(2)} AM",
                extra={"operation": "scheduler"}
            )
        except Exception as e:
            uvicorn_logger.error(
                f"Scheduler initialization failed: {str(e)}",
                exc_info=True,
                extra={"operation": "scheduler"}
            )


def _init_pg_cron_jobs():
    with Session(engine) as session:
        try:
            # Check for pg_cron extension
            session.execute(text("CREATE EXTENSION IF NOT EXISTS pg_cron"))

            # Verify existing jobs
            cron_job = session.execute(text(
                "SELECT jobid FROM cron.job WHERE jobname = 'clean_old_login_times'"
            )).first()

            if not cron_job:
                session.execute(text(
                    """SELECT cron.schedule(
                        'clean_old_login_times',
                        '0 3 * * *',
                        $$DELETE FROM tb_app_login_times WHERE create_time < NOW() - INTERVAL '31 days'$$
                    )"""
                ))
                session.commit()
                uvicorn_logger.info(
                    "pg_cron job created: clean_old_login_times",
                )

        except NotSupportedError:
            uvicorn_logger.warning("pg_cron extension unavailable, falling back to application scheduler")
            _init_scheduler()
        except Exception as e:
            uvicorn_logger.error(f"pg_cron initialization error: {str(e)}")
            session.rollback()
            _init_scheduler()


def init_db():
    """
    初始化数据库相关操作
    :return:
    """
    try:
        _init_database_tables()
        _init_super_admin()
        _init_pg_cron_jobs()
    except OperationalError as e:
        uvicorn_logger.error(e)


def get_session():
    """
    数据库会话依赖注入
    """
    with Session(engine) as session:
        yield session


SessionDep = Annotated[Session, Depends(get_session)]
